﻿using System;
using System.Collections.Generic;

using System.Text;
using Portal.Interface;
using Portal.Environment;
using Portal.Language;
using System.Data;
using System.Data.SqlClient;

namespace Portal.CMS
{
    public class LawDocumentSignerBLL
    {
        public string ConnectionString
        {
            get
            {
                return Environments.ConnectionString;
            }
        }

        public LawDocumentSignerCls CreateModel(string LawDocumentSignerId)
        {
            string Query = " select TableLawDocumentSigner.* from TableLawDocumentSigner where LawDocumentSignerId = '" + LawDocumentSignerId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(ConnectionString));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            SqlDataReader SqlDataReader = SqlCommand.ExecuteReader();
            if (!SqlDataReader.HasRows)
            {
                throw new Exception("Không tìm thấy bản ghi LawDocumentSignerId có mã: " + LawDocumentSignerId);
            }

            SqlDataReader.Read();
            LawDocumentSignerCls tLawDocumentSignerCls = new LawDocumentSignerCls();
            tLawDocumentSignerCls.LawDocumentSignerId = SqlDataReader.GetString(SqlDataReader.GetOrdinal("LawDocumentSignerId"));
            tLawDocumentSignerCls.Subject = SqlDataReader.GetString(SqlDataReader.GetOrdinal("Subject"));

            return tLawDocumentSignerCls;
        }



        public DataTable ReadingLawDocumentSigner(string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FuncUtilities.CorrectForSearch(Keyword);
            string Query = "select LawDocumentSignerId,Subject from TableLawDocumentSigner where 1=1 ";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and Subject like N'%" + Keyword + "%'";
            }
            Query += " order by [Subject]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(ConnectionString));

            da.Fill(dataTable);
            return dataTable;
        }


        public string CreateLawDocumentSigner(
            SqlTransaction _SqlTransaction,
            string Subject)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewLawDocumentSignerId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            
            string Query = "Insert into TableLawDocumentSigner(LawDocumentSignerId,Subject) values (@LawDocumentSignerId,@Subject)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentSignerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentSignerId"].Value = NewLawDocumentSignerId;

            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewLawDocumentSignerId;
        }



        public void UpdateLawDocumentSigner(SqlTransaction _SqlTransaction,
            string LawDocumentSignerId,
            string Subject)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            
            string Query = "Update TableLawDocumentSigner set Subject=@Subject where LawDocumentSignerId=@LawDocumentSignerId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentSignerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentSignerId"].Value = LawDocumentSignerId;

            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void DeleteLawDocumentSigner(SqlTransaction _SqlTransaction,
            string LawDocumentSignerId)
        {
            LawDocumentSignerCls oLawDocumentSignerCls = new LawDocumentSignerBLL().CreateModel(LawDocumentSignerId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Delete TableLawDocumentSigner where LawDocumentSignerId=@LawDocumentSignerId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentSignerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentSignerId"].Value = LawDocumentSignerId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();

                string SubjectDeleted = "";
                if (oLawDocumentSignerCls != null)
                {
                    SubjectDeleted = oLawDocumentSignerCls.Subject;
                }
                Environments.AddDeletedRecordForSync(_SqlTransaction, "TableLawDocumentSigner","Người ký văn bản", LawDocumentSignerId, SubjectDeleted);
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }
    }
}
